﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using OpenMVCRM.Entities;

namespace OpenMVCRM.DataAccess
{
    public class Class1
    {
    }
}
namespace OpenMVCRM.DataAccess
{
    public class CompanyDA : BaseDA
    {
        public static IList<Country> GetCountries()
        {
            List<Country> clientList = new List<Country>();
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "uspGetAllCountries";

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int idLocation = reader.GetOrdinal("CountryID");
                            int nameLocation = reader.GetOrdinal("CountryName");

                            while (reader.Read())
                            {
                                var client = new Country();
                                client.CountryID = reader.GetInt32(idLocation);
                                if (!reader.IsDBNull(nameLocation))
                                    client.CountryName = reader.GetString(nameLocation);

                                clientList.Add(client);
                            }
                        }
                    }
                }

            }
            return clientList;
        }

        public static IList<State> GetStates(int countryId)
        {
            List<State> clientList = new List<State>();
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "uspGetStates";
                    cmd.Parameters.AddWithValue("@countryid", countryId);
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int idLocation = reader.GetOrdinal("StateID");
                            int nameLocation = reader.GetOrdinal("StateName");

                            while (reader.Read())
                            {
                                var client = new State();
                                client.StateID = reader.GetInt32(idLocation);
                                if (!reader.IsDBNull(nameLocation))
                                    client.StateName = reader.GetString(nameLocation);

                                clientList.Add(client);
                            }
                        }
                    }
                }

            }
            return clientList;
        }


        public static IList<Industry> GetIndustries()
        {
            List<Industry> clientList = new List<Industry>();
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "uspGetAllIndustries";

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int idLocation = reader.GetOrdinal("ID");
                            int nameLocation = reader.GetOrdinal("Name");

                            while (reader.Read())
                            {
                                var client = new Industry();
                                client.ID = reader.GetInt32(idLocation);
                                if (!reader.IsDBNull(nameLocation))
                                    client.Name = reader.GetString(nameLocation);

                                clientList.Add(client);
                            }
                        }
                    }
                }

            }
            return clientList;
        }

        public static Client GetClientFromID(int id)
        {
            Client client = null;
            using (var conn = new SqlConnection(connString))
            {
                using (SqlCommand cmd = new SqlCommand("uspGetCompanyFromID", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "uspGetCompanyFromID";
                    cmd.Parameters.AddWithValue("@id", id);
                    conn.Open();
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            client = new Client();

                            int idLocation = reader.GetOrdinal("ID");
                            int nameLocation = reader.GetOrdinal("Name");
                            int descrLocation = reader.GetOrdinal("Description");
                            int websiteLocation = reader.GetOrdinal("WebSite");
                            int industryIdLocation = reader.GetOrdinal("IndustryID");
                            int industryNameLocation = reader.GetOrdinal("IndustryName");
                            int createdDateLocation = reader.GetOrdinal("CreatedDate");
                            int modifedDateLocation = reader.GetOrdinal("ModifiedDate");
                            int primaryPhoneLocation = reader.GetOrdinal("PrimaryPhoneNumber");

                            reader.Read();

                            client.ID = reader.GetInt32(idLocation);
                            if (!reader.IsDBNull(nameLocation))
                                client.Name = reader.GetString(nameLocation);

                            if (!reader.IsDBNull(websiteLocation))
                                client.WebSite = reader.GetString(websiteLocation);

                            if (!reader.IsDBNull(descrLocation))
                                client.Description = reader.GetString(descrLocation);

                            if (!reader.IsDBNull(industryIdLocation))
                                client.Industry.ID = reader.GetInt32(industryIdLocation);

                            if (!reader.IsDBNull(industryNameLocation))
                                client.Industry.Name = reader.GetString(industryNameLocation);

                            if (!reader.IsDBNull(createdDateLocation))
                                client.CreatedDate = reader.GetDateTime(createdDateLocation);

                            if (!reader.IsDBNull(modifedDateLocation))
                                client.ModifiedDate = reader.GetDateTime(modifedDateLocation);

                            if (!reader.IsDBNull(primaryPhoneLocation))
                                client.PrimaryPhoneNumber.Number = reader.GetString(primaryPhoneLocation);

                        }
                    }
                }
            }
            return client;


        }

        public static IList<Client> GetClientsForOrganization(int organizationId)
        {
            List<Client> clientList = new List<Client>();
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "uspGetAllClientsForOrganization";
                    cmd.Parameters.AddWithValue("@orgId", organizationId);

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {

                            int idLocation = reader.GetOrdinal("ID");
                            int nameLocation = reader.GetOrdinal("Name");
                            int descrLocation = reader.GetOrdinal("Description");
                            int websiteLocation = reader.GetOrdinal("WebSite");
                            int industryIdLocation = reader.GetOrdinal("IndustryID");
                            int industryNameLocation = reader.GetOrdinal("IndustryName");
                            int createdDateLocation = reader.GetOrdinal("CreatedDate");


                            while (reader.Read())
                            {
                                var client = new Client();
                                client.ID = reader.GetInt32(idLocation);
                                if (!reader.IsDBNull(nameLocation))
                                    client.Name = reader.GetString(nameLocation);

                                if (!reader.IsDBNull(websiteLocation))
                                    client.WebSite = reader.GetString(websiteLocation);

                                if (!reader.IsDBNull(descrLocation))
                                    client.Description = reader.GetString(descrLocation);

                                if (!reader.IsDBNull(industryIdLocation))
                                    client.Industry.ID = reader.GetInt32(industryIdLocation);

                                if (!reader.IsDBNull(industryNameLocation))
                                    client.Industry.Name = reader.GetString(industryNameLocation);

                                if (!reader.IsDBNull(createdDateLocation))
                                    client.CreatedDate = reader.GetDateTime(createdDateLocation);

                                clientList.Add(client);
                            }
                        }
                    }
                }

            }


            return clientList;


        }


        public static OperationStatus SaveCompany(Company company)
        {

            var opStatus = new OperationStatus();

            using (var conn = new SqlConnection(connString))
            {
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "uspSaveCompany";
                    cmd.Parameters.AddWithValue("@Id", company.ID);
                    cmd.Parameters.AddWithValue("@name", company.Name);
                    cmd.Parameters.AddWithValue("@description", (object)company.Description ?? DBNull.Value);
                    cmd.Parameters.AddWithValue("@website", company.WebSite);
                    cmd.Parameters.AddWithValue("@industryID", company.IndustryID);
                    cmd.Parameters.AddWithValue("@createdBy", company.CreatedBy.ID);
                    cmd.Parameters.AddWithValue("@orgId", company.OrganizationID);
                    cmd.Parameters.AddWithValue("@type", company.Type.ToString());

                    DbParameter returnValue;
                    returnValue = cmd.CreateParameter();
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(returnValue);

                    conn.Open();
                    cmd.ExecuteNonQuery();
                    int result = Convert.ToInt32(returnValue.Value);
                    conn.Close();
                    opStatus.OperationID = result;
                    opStatus.Status = true;


                }
            }

            return opStatus;
        }



    }

    public class BaseDA
    {
        protected readonly static string connString = ConfigurationManager.ConnectionStrings["CRMContext"].ConnectionString;

    }
}